﻿using System;
using System.Collections.Generic;
using Remind.Model;
using Remind.DBUtility;
using System.Data;
using System.Data.OleDb;
using Microsoft.SqlServer.Server;

namespace Remind.AccessDAL
{
    public class Report
    {
        protected const string SQL_SELECT_SELECTALLINFO = "SELECT cname, age,cbirthday, sex, IDcard, cbz,gname, phone, phone1, telfamily, telcompany, fax, addrfamily, addrcompany, other, email, qq, msn, blog, website FROM selectallinfo";
        protected const string SQL_SELECT_SELECTALLINFO_LIKE_CNAME = "SELECT cname,age, cbirthday, sex, IDcard, cbz,gname, phone, phone1, telfamily, telcompany, fax, addrfamily, addrcompany, other, email, qq, msn, blog, website FROM selectallinfo WHERE cname like @cname";
        protected const string SQL_SELECT_SELECTALLINFO_BY_GNAME = "SELECT cname,age, cbirthday, sex, IDcard, cbz,gname, phone, phone1, telfamily, telcompany, fax, addrfamily, addrcompany, other, email, qq, msn, blog, website FROM selectallinfo WHERE gname = @gname";
        
        public IList<ReportInfo> GetAllInfo()
        {
            IList<ReportInfo> reports = new List<ReportInfo>();
            using (OleDbDataReader rdr=AccessHelper.ExecuteReader(AccessHelper.ConnectionString,CommandType.Text,SQL_SELECT_SELECTALLINFO,null))
            {
                ReportInfo report = null;
                report = PachageReports(reports, rdr, report);
                return reports;
            }
        }
        public IList<ReportInfo> GetReportInfoByCname(string cname)
        {
            OleDbParameter parm = new OleDbParameter("@cname", OleDbType.VarChar, 16);
            parm.Value = cname;
            IList<ReportInfo> reports = new List<ReportInfo>();
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_SELECTALLINFO_LIKE_CNAME, parm))
            {
                ReportInfo report = null;
                report = PachageReports(reports, rdr, report);
                return reports;
            }
        }
        public IList<ReportInfo> GetReportInfoByGname(string gname)
        {
            OleDbParameter parm = new OleDbParameter("@gname", OleDbType.VarChar, 16);
            parm.Value = gname;
            IList<ReportInfo> reports = new List<ReportInfo>();
            using (OleDbDataReader rdr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, SQL_SELECT_SELECTALLINFO_BY_GNAME, parm))
            {
                ReportInfo report = null;
                report = PachageReports(reports, rdr, report);
                return reports;
            }
        }
        private static ReportInfo PachageReports(IList<ReportInfo> reports, OleDbDataReader rdr, ReportInfo report)
        {
            while (rdr.Read())
            {
                report = new ReportInfo();
                report.Cname = rdr.GetString(rdr.GetOrdinal("cname"));
                report.Age = rdr.GetInt32(rdr.GetOrdinal("age"));
                report.Cbirthday = rdr.GetDateTime(rdr.GetOrdinal("cbirthday"));
                report.Sex = rdr.GetString(rdr.GetOrdinal("sex"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("IDcard")))
                    report.Idcard = rdr.GetString(rdr.GetOrdinal("IDcard"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("gname")))
                    report.Gname = rdr.GetString(rdr.GetOrdinal("gname"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("phone")))
                    report.Phone = rdr.GetString(rdr.GetOrdinal("phone"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("phone1")))
                    report.Phone1 = rdr.GetString(rdr.GetOrdinal("phone1"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("telfamily")))
                    report.Telfamily = rdr.GetString(rdr.GetOrdinal("telfamily"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("telcompany")))
                    report.Telcompany = rdr.GetString(rdr.GetOrdinal("telcompany"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("fax")))
                    report.Fax = rdr.GetString(rdr.GetOrdinal("fax"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("addrfamily")))
                    report.Addrfamily = rdr.GetString(rdr.GetOrdinal("addrfamily"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("addrcompany")))
                    report.Addrcompany = rdr.GetString(rdr.GetOrdinal("addrcompany"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("other")))
                    report.Other = rdr.GetString(rdr.GetOrdinal("other"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("email")))
                    report.Email = rdr.GetString(rdr.GetOrdinal("email"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("qq")))
                    report.Qq = rdr.GetString(rdr.GetOrdinal("qq"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("msn")))
                    report.Msn = rdr.GetString(rdr.GetOrdinal("msn"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("blog")))
                    report.Blog = rdr.GetString(rdr.GetOrdinal("blog"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("website")))
                    report.WebSite = rdr.GetString(rdr.GetOrdinal("website"));
                if (!rdr.IsDBNull(rdr.GetOrdinal("cbz")))
                    report.CBz = rdr.GetString(rdr.GetOrdinal("cbz"));
                reports.Add(report);
                report = null;
            }
            return report;
        }
    }
}
